QuickSight で別リージョンの Redshift のデータを可視化する

QuickSight で別リージョンの Redshift のデータを可視化する

QuickSight で別リージョンの Redshift のデータを可視化しました。
Clock Icon2024.11.10

コーヒーが好きな emi です。

QuickSight で別リージョンの Redshift のデータを可視化してみました。手順を紹介します。

0. 構成図

東京リージョンで QuickSight を有効化し、バージニア北部リージョンで Redsfhit Serverless を構築します。
東京リージョンの VPC とバージニア北部リージョンの VPC は VPC Peering で接続します。
visualize-redshift-in-another-region-from-quicksight_1

1. ネットワーク設定

VPC、VPC ピアリング、セキュリティグループを作成します。
visualize-redshift-in-another-region-from-quicksight_2

1-1. VPC の作成

バージニア北部リージョンでは以下を作成します。

項目 設定値 備考
VPC 10.0.0.0/16 Virginia-vpc
パブリックサブネット 1 つ(10.0.2.0/24/24) Redshift Serverless を操作する EC2 を配置するため
プライベートサブネット 3 つ(10.0.1.0/24、10.0.2.0/24、10.0.3.0/24) Redshift Serverless を構築するのに必要なため
S3 ゲートウェイエンドポイント プライベートサブネットのルートテーブルには S3 ゲートウェイエンドポイントへのルートを追加 S3 に配置したサンプルデータを Redshift にコピーするため

visualize-redshift-in-another-region-from-quicksight_3

東京リージョンでは以下を作成します。

項目 設定値 備考
VPC 172.16.0.0/16 Tokyo-vpc
プライベートサブネット 2 つ(172.16.11.0/24、172.16.12.0/24) QuickSight 用 ENI を配置するため

visualize-redshift-in-another-region-from-quicksight_4

1-2. VPC ピアリングの作成

VPC コンソールの「ピアリング接続」から、VPC ピアリングを作成します。どちらのリージョンからでも良いと思いますが、今回は東京リージョンのコンソールから作成しました。

項目 設定値
名前 quicksight-redshift-peering
ピアリング接続するローカル VPC を選択
VPC ID (リクエスタ) vpc-xxxxxxxxxxxxxxxxx (Tokyo-vpc)
ピアリング接続するもう一方の VPC を選択
アカウント 自分のアカウント
リージョン 米国東部 (バージニア北部) (us-east-1)
VPC ID (アクセプタ) vpc-yyyyyyyyyyyyyyyyy (Virginia-vpc)
タグ key:Name、value:quicksight-redshift-peering

visualize-redshift-in-another-region-from-quicksight_5

バージニア北部リージョンの VPC コンソールに移動し、VPC ピアリングを開くと、「承認の保留中」となっている VPC ピアリングが表示されるようになっています。これを承認します。

visualize-redshift-in-another-region-from-quicksight_6

承認したら、DNS 設定を有効にしておきます。これから作成する Redshift Serverless のホスト名を名前解決したいからです。
バージニア北部リージョンの VPC ピアリングの DNS タブを開き、「アクセプタ DNS 解決」のチェックを入れ設定を保存します。
visualize-redshift-in-another-region-from-quicksight_7

visualize-redshift-in-another-region-from-quicksight_8

visualize-redshift-in-another-region-from-quicksight_9

東京リージョン側の DNS 設定も有効化しておきます。
visualize-redshift-in-another-region-from-quicksight_10

バージニア北部リージョンと東京リージョン間で通信のルーティングができるよう、ルートテーブルを変更します。

バージニア北部リージョンのプライベートサブネットのルートテーブルに以下ルートを追加します。

送信先 ターゲット
172.16.0.0/16(東京リージョンの CIDR) ピアリング接続 ID(pcx-0b3bad015e2a6c22a)

visualize-redshift-in-another-region-from-quicksight_11

東京リージョンのプライベートサブネットのルートテーブルに以下ルートを追加します。

送信先 ターゲット
10.0.0.0/16(バージニア北部リージョンの CIDR) ピアリング接続 ID(pcx-0b3bad015e2a6c22a)

visualize-redshift-in-another-region-from-quicksight_12

1-3. セキュリティグループの作成

バージニア北部リージョンで以下のセキュリティグループを作成します。

  • Redshift の操作を行う EC2 のセキュリティグループ(client-sg)
IN/OUT タイプ プロトコル ポート 送信先 説明
アウトバウンド HTTPS TCP 443 0.0.0.0/0 to SSM(セッションマネージャーで接続するため)
アウトバウンド Redshift TCP 5439 Redshift-serverless-sg to Redshift
  • Redshift Serverless のセキュリティグループ(redshift-serverless-sg)
IN/OUT タイプ プロトコル ポート 送信先 説明
インバウンド Redshift TCP 5439 172.16.0.0/16 from Tokyo
インバウンド Redshift TCP 5439 client-sg from client-sg
アウトバウンド すべてのトラフィック すべて すべて 0.0.0.0/0 -

東京リージョンで以下のセキュリティグループを作成します。

  • QuickSight 用のセキュリティグループ(quicksight-sg)
IN/OUT タイプ プロトコル ポート 送信先 説明
インバウンド すべての TCP TCP 0 - 65535 172.16.0.0/16 local
インバウンド カスタム TCP TCP 32768 - 65535 10.0.0.0/16 from Virginia(東京からアクセスした QuickSight のクエリ通信の戻り)
アウトバウンド すべてのトラフィック すべて すべて 0.0.0.0/0 -

2. Redshift の操作を行う EC2 の作成

Redshift を psql で操作するための EC2 を作成します。
visualize-redshift-in-another-region-from-quicksight_13

項目 設定値 備考
名前 rs-client 任意の名前
AMI ID ami-06b21ccaeff8cd686 al2023-ami-2023.6.20241010.0-kernel-6.1-x86_64
インスタンスタイプ t3.micro
キーペア なし
セキュリティグループ client-sg
IAMロール AmazonSSMManagedInstanceCoreRole 「AmazonSSMManagedInstanceCore」を付与(SSM セッションマネージャー使用のため)

作成できたら、EC2 にセッションマネージャーで接続します。
以下のブログを参考に、psql をインストールします。
https://dev.classmethod.jp/articles/learning-with-technical-support-postgresql-1/

まずは、yum search postgresql を実行し EC2(Amazon Linux 2023)のリポジトリに PostgreSQL のパッケージが用意されているか確認をしてみます。

yum search postgresql の実行結果
sh-5.2$ yum search postgresql
Amazon Linux 2023 repository                                                                                                                                                                   42 MB/s |  28 MB     00:00
Amazon Linux 2023 Kernel Livepatch repository                                                                                                                                                  59 kB/s |  11 kB     00:00

============================================================================================= Name & Summary Matched: postgresql =============================================================================================
collectd-postgresql.x86_64 : PostgreSQL plugin for collectd
postgresql-odbc.x86_64 : PostgreSQL ODBC driver
postgresql15.x86_64 : PostgreSQL client programs
postgresql15-contrib.x86_64 : Extension modules distributed with PostgreSQL
postgresql15-docs.x86_64 : Extra documentation for PostgreSQL
postgresql15-llvmjit.x86_64 : Just-in-time compilation support for PostgreSQL
postgresql15-plperl.x86_64 : The Perl procedural language for PostgreSQL
postgresql15-plpython3.x86_64 : The Python3 procedural language for PostgreSQL
postgresql15-pltcl.x86_64 : The Tcl procedural language for PostgreSQL
postgresql15-private-devel.x86_64 : PostgreSQL development header files for this build of PostgreSQL server
postgresql15-private-libs.x86_64 : The shared libraries required only for this build of PostgreSQL server
postgresql15-server.x86_64 : The programs needed to create and run a PostgreSQL server
postgresql15-server-devel.x86_64 : PostgreSQL development header files and libraries
postgresql15-static.x86_64 : Statically linked PostgreSQL libraries
postgresql15-test.x86_64 : The test suite distributed with PostgreSQL
postgresql15-test-rpm-macros.noarch : Convenience RPM macros for build-time testing against PostgreSQL server
postgresql15-upgrade.x86_64 : Support for upgrading from the previous major release of PostgreSQL
postgresql16.x86_64 : PostgreSQL client programs
postgresql16-contrib.x86_64 : Extension modules distributed with PostgreSQL
postgresql16-docs.x86_64 : Extra documentation for PostgreSQL
postgresql16-llvmjit.x86_64 : Just-in-time compilation support for PostgreSQL
postgresql16-plperl.x86_64 : The Perl procedural language for PostgreSQL
postgresql16-plpython3.x86_64 : The Python3 procedural language for PostgreSQL
postgresql16-pltcl.x86_64 : The Tcl procedural language for PostgreSQL
postgresql16-private-devel.x86_64 : PostgreSQL development header files for this build of PostgreSQL server
postgresql16-private-libs.x86_64 : The shared libraries required only for this build of PostgreSQL server
postgresql16-server.x86_64 : The programs needed to create and run a PostgreSQL server
postgresql16-server-devel.x86_64 : PostgreSQL development header files and libraries
postgresql16-static.x86_64 : Statically linked PostgreSQL libraries
postgresql16-test.x86_64 : The test suite distributed with PostgreSQL
postgresql16-test-rpm-macros.noarch : Convenience RPM macros for build-time testing against PostgreSQL server
postgresql16-upgrade.x86_64 : Support for upgrading from the previous major release of PostgreSQL
================================================================================================== Name Matched: postgresql ==================================================================================================
postgresql-odbc-tests.x86_64 : Testsuite files for psqlodbc
postgresql15-upgrade-devel.x86_64 : Support for build of extensions required for upgrade process
postgresql16-upgrade-devel.x86_64 : Support for build of extensions required for upgrade process
================================================================================================ Summary Matched: postgresql =================================================================================================
BabelfishDump.x86_64 : Postgresql dump utilities modified for Babelfish
apr-util-pgsql.x86_64 : APR utility library PostgreSQL DBD driver
libpgtypes.x86_64 : Map PostgreSQL database types to C equivalents
libpq.x86_64 : PostgreSQL client library
libpq-devel.x86_64 : Development files for building PostgreSQL client tools
perl-DBD-Pg.x86_64 : A PostgreSQL interface for Perl
perl-DateTime-Format-Pg.noarch : Parse and format PostgreSQL dates and times
php8.1-pgsql.x86_64 : A PostgreSQL database module for PHP 8.1
php8.2-pgsql.x86_64 : A PostgreSQL database module for PHP 8.2
php8.3-pgsql.x86_64 : A PostgreSQL database module for PHP 8.3
postfix-pgsql.x86_64 : Postfix PostgreSQL map support
python-psycopg2-doc.x86_64 : Documentation for psycopg python PostgreSQL database adapter
python3-psycopg2.x86_64 : A PostgreSQL database adapter for Python 3
python3-psycopg2-debug.x86_64 : A PostgreSQL database adapter for Python 3 (debug build)
python3-psycopg2-tests.x86_64 : A testsuite for A PostgreSQL database adapter for Python 2
sh-5.2$

postgresql16.x86_64 : PostgreSQL client programs と表示されており、psql メジャーバージョン 16 をインストールできそうです。
では、以下のコマンドで psql クライアントをインストールします。

sudo yum install postgresql16
実行結果(途中 y 入力)
sh-5.2$ sudo yum install postgresql16
Last metadata expiration check: 0:08:42 ago on Wed Nov  6 08:27:07 2024.
Dependencies resolved.
==============================================================================================================================================================================================================================
 Package                                                        Architecture                                Version                                                    Repository                                        Size
==============================================================================================================================================================================================================================
Installing:
 postgresql16                                                   x86_64                                      16.4-1.amzn2023.0.1                                        amazonlinux                                      1.8 M
Installing dependencies:
 postgresql16-private-libs                                      x86_64                                      16.4-1.amzn2023.0.1                                        amazonlinux                                      144 k

Transaction Summary
==============================================================================================================================================================================================================================
Install  2 Packages

Total download size: 2.0 M
Installed size: 8.0 M
Is this ok [y/N]: y
Downloading Packages:
(1/2): postgresql16-private-libs-16.4-1.amzn2023.0.1.x86_64.rpm                                                                                                                               753 kB/s | 144 kB     00:00
(2/2): postgresql16-16.4-1.amzn2023.0.1.x86_64.rpm                                                                                                                                            8.0 MB/s | 1.8 MB     00:00
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                                                                                         6.0 MB/s | 2.0 MB     00:00
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                                                                                                                                      1/1
  Installing       : postgresql16-private-libs-16.4-1.amzn2023.0.1.x86_64                                                                                                                                                 1/2
  Installing       : postgresql16-16.4-1.amzn2023.0.1.x86_64                                                                                                                                                              2/2
  Running scriptlet: postgresql16-16.4-1.amzn2023.0.1.x86_64                                                                                                                                                              2/2
  Verifying        : postgresql16-16.4-1.amzn2023.0.1.x86_64                                                                                                                                                              1/2
  Verifying        : postgresql16-private-libs-16.4-1.amzn2023.0.1.x86_64                                                                                                                                                 2/2
==============================================================================================================================================================================================================================
WARNING:
  A newer release of "Amazon Linux" is available.

  Available Versions:

  Version 2023.6.20241028:
    Run the following command to upgrade to 2023.6.20241028:

      dnf upgrade --releasever=2023.6.20241028

    Release notes:
     https://docs.aws.amazon.com/linux/al2023/release-notes/relnotes-2023.6.20241028.html

  Version 2023.6.20241031:
    Run the following command to upgrade to 2023.6.20241031:

      dnf upgrade --releasever=2023.6.20241031

    Release notes:
     https://docs.aws.amazon.com/linux/al2023/release-notes/relnotes-2023.6.20241031.html

==============================================================================================================================================================================================================================

Installed:
  postgresql16-16.4-1.amzn2023.0.1.x86_64                                                                 postgresql16-private-libs-16.4-1.amzn2023.0.1.x86_64

Complete!
sh-5.2$

psql のバージョンを確認します。

psql --version

▼実行結果

sh-5.2$ psql --version
psql (PostgreSQL) 16.4
sh-5.2$

Redshift を操作するための EC2 インスタンスの準備ができました。

3. Redshift の作成

今回は Redshift コンソールから Redshift Serverless を作成します。

visualize-redshift-in-another-region-from-quicksight_15

「設定をカスタマイズ」で以下の設定をおこないます。

項目 設定値 備考
名前空間 20241106-namespace 任意の名前
データベース名 dev 変更不可
管理者ユーザー名 admin
管理者パスワード AWS Secrets Manager で管理者認証情報を管理する
IAM ロール AmazonRedshift-CommandsAccessRole-20241106T170516 ロールタイプ:デフォルト。デフォルトの IAM ロールを作成する
暗号化設定をカスタマイズする (高度) チェックなし
監査ログ記録 ユーザーログ、接続ログ、ユーザーアクティビティログ CloudWatch Logs に保存される
ワークグループの名前 20241106-workgroup 任意の名前
ベース容量 8 Redshift Serverless で設定できる制限について
Virtual Private Cloud (VPC) vpc-03e89ff4c1ba11cee Virginia-vpc
VPC セキュリティグループ redshift-serverless-sg
サブネット Virginia-vpc に作成した 3 つのプライベートサブネット ID を選択
拡張された VPC のルーティング チェック 拡張された VPC ルーティングを有効にしないと、Redshift は AWS ネットワーク内のサービス(S3 バケットなど)と AWS 独自のグローバルネットワーク経由で通信する。

管理者パスワードを Secrets Manager 管理に設定したため、バージニア北部リージョンの Secrets Manager にシークレットが作成されています。
visualize-redshift-in-another-region-from-quicksight_14

「sqlworkbench!~」から始まるシークレットは、クエリエディタ v2 での接続などに使用されます。
「redshift!20241106-namespace-admin」の方が、今回作成した Redshift Serverless のシークレットです。

4. サンプルデータの準備

S3 にサンプルデータを置いて Redshift にコピーします。

visualize-redshift-in-another-region-from-quicksight_16

S3 バケットはデフォルトの設定で作成します。Redshift と同じバージニア北部リージョンに作成しました。

今回は以下ドキュメントのサンプルデータ「TICKIT」を使用します。
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/c_sampledb.html

以前はドキュメントに「tickitdb.zip」というリンクが貼ってあったのですが、現在は見当たらなくなってしまったようです[1]。以下に「tickitdb.zip」をダウンロードするリンクを貼っておきますので、ここからダウンロードしてください。クリックするとすぐに zip ファイルがダウンロードされます。

tickitdb.zip をダウンロードしたら、展開して「tickitdb」フォルダごと S3 バケット直下に配置します。

visualize-redshift-in-another-region-from-quicksight_17
visualize-redshift-in-another-region-from-quicksight_18

ここまでの構成図では省略してしまっていましたが、VPC 作成時に一緒に作成した S3 ゲートウェイエンドポイントのエンドポイントポリシーも必要であれば設定しておきましょう。デフォルトではすべて許可になっていますので、今回の手順では設定しなくても大丈夫です。

visualize-redshift-in-another-region-from-quicksight_19

S3 ゲートウェイエンドポイントのエンドポイントポリシーを、特定の Redshift から特定の S3 バケットのみ許可する設定の例

デフォルトでは以下のようにすべて許可になっていますので、

{
	"Version": "2008-10-17",
	"Statement": [
		{
			"Effect": "Allow",
			"Principal": "*",
			"Action": "*",
			"Resource": "*"
		}
	]
}

以下のように変更できます。

{
	"Version": "2012-10-17",
	"Statement": [
		{
			"Effect": "Allow",
			"Principal": "*",
			"Action": "s3:*",
			"Resource": [
				"arn:aws:s3:::redshift-data-emiki",
				"arn:aws:s3:::redshift-data-emiki/*"
			],
			"Condition": {
				"ArnEquals": {
					"aws:PrincipalArn": "arn:aws:iam::123456789912:role/service-role/AmazonRedshift-CommandsAccessRole-20241106T170516"
				}
			}
		}
	]
}

visualize-redshift-in-another-region-from-quicksight_20

続いて Redshift の IAM ロールに付与された IAM ポリシーを編集します。
Redshift Serverless 作成時にデフォルトで作成され、Redshift Serverless に付与されている IAM ロールに、「AmazonRedshift-CommandsAccessPolicy-xxxxxxxxxxxxxxx」という IAM ポリシーが設定されています。「AmazonRedshift-CommandsAccessPolicy-xxxxxxxxxxxxxxx」を以下のように変更します。

AmazonRedshift-CommandsAccessPolicy-xxxxxxxxxxxxxxx
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Action": [
                "s3:GetObject",
                "s3:GetBucketAcl",
                "s3:GetBucketCors",
                "s3:GetEncryptionConfiguration",
                "s3:GetBucketLocation",
                "s3:ListBucket",
                "s3:ListAllMyBuckets",
                "s3:ListMultipartUploadParts",
                "s3:ListBucketMultipartUploads",
                "s3:PutObject",
                "s3:PutBucketAcl",
                "s3:PutBucketCors",
                "s3:DeleteObject",
                "s3:AbortMultipartUpload",
                "s3:CreateBucket"
            ],
            "Effect": "Allow",
            "Resource": [
                "arn:aws:s3:::redshift-data-emiki/*",
                "arn:aws:s3:::redshift-data-emiki"
            ]
        }
    ]
}

これは Redshift Serverless が、サンプルデータを配置した S3 バケットにアクセスするためのポリシーです。

では、Redshift Serverless に接続します。EC2 にセッションマネージャーでログインし、psql で Redshift Serverless にアクセスします。Redshift Serverless のコンソールで、ワークグループからエンドポイントをコピーしておきます。以下のようなエンドポイントがコピーできるはずです。

<ワークグループ名>.123456789012.us-east-1.redshift-serverless.amazonaws.com:5439/dev

末尾の :5439/dev は接続ポートと最初のデータベース名です。その前の部分が Redshift Serverless の接続に使うホスト名です。

では、以下のコマンドで Redshift にログインします。

psql -h <ホスト名> -U admin -d dev -p 5439

▼実行結果

sh-5.2$ psql -h 20241106-workgroup.123456789012.us-east-1.redshift-serverless.amazonaws.com -U admin -d dev -p 5439
Password for user admin:
psql (16.4, server 8.0.2)
WARNING: psql major version 16, server major version 8.0.
         Some psql features might not work.
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, compression: off)
Type "help" for help.

dev=#

パスワードを求められたら、Secrets Manager に保存されているシークレットを入力してください。

現在のスキーマを確認します。

select current_schema();

▼実行結果

dev=# select current_schema();
 current_schema
----------------
 public
(1 row)

dev=#

dev データベースではデフォルトで public というスキーマが使われています。今回はこのまま使用します。

Amazon S3 からデータをロードする を参考に、Redshift 側にテーブルを作っておきます。今回は users テーブルだけ作成しましょう。

create table users(
userid integer not null distkey sortkey,
username char(8),
firstname varchar(30),
lastname varchar(30),
city varchar(30),
state char(2),
email varchar(100),
phone char(14),
likesports boolean,
liketheatre boolean,
likeconcerts boolean,
likejazz boolean,
likeclassical boolean,
likeopera boolean,
likerock boolean,
likevegas boolean,
likebroadway boolean,
likemusicals boolean);    
実行結果
dev=# create table users(
userid integer not null distkey sortkey,
username char(8),
firstname varchar(30),
lastname varchar(30),
city varchar(30),
state char(2),
email varchar(100),
phone char(14),
likesports boolean,
liketheatre boolean,
likeconcerts boolean,
likejazz boolean,
likeclassical boolean,
likeopera boolean,
likerock boolean,
likevegas boolean,
likebroadway boolean,
likemusicals boolean);
CREATE TABLE
dev=#

以下のコマンドで、空っぽのテーブルができていることを確認します。

select * from users;

▼実行結果

dev=# select * from users;
 userid | username | firstname | lastname | city | state | email | phone | likesports | liketheatre | likeconcerts | likejazz | likeclassical | likeopera | likerock | likevegas | likebroadway | likemusicals
--------+----------+-----------+----------+------+-------+-------+-------+------------+-------------+--------------+----------+---------------+-----------+----------+-----------+--------------+--------------
(0 rows)

dev=#

COPY コマンドで、S3 から Redshift にデータをコピーします。

実行コマンド

copy users from 's3://<S3 バケット名>/tickitdb/allusers_pipe.txt' 
iam_role 'arn:aws:iam::123456789012:role/service-role/AmazonRedshift-CommandsAccessRole-xxxxxxxxxxxxxxx'
delimiter '|' region 'us-east-1';

s3://<S3 バケット名>/tickitdb/allusers_pipe.txt は、S3 に配置した「allusers_pipe.txt」の S3 URI です。IAM ロールは Redshift Serverless に付与されている「AmazonRedshift-CommandsAccessRole-xxxxxxxxxxxxxxx」を入力します。今回はバージニア北部リージョンで作業しているので、リージョンは us-east-1 です。数秒待つと以下のようにコピーが終了します。

▼実行結果

dev=# copy users from 's3://redshift-data-emiki/tickitdb/allusers_pipe.txt'
iam_role 'arn:aws:iam::123456789012:role/service-role/AmazonRedshift-CommandsAccessRole-20241106T170516'
delimiter '|' region 'us-east-1';
INFO:  Load into table 'users' completed, 49990 record(s) loaded successfully.
COPY
dev=#

以下のコマンドで、users テーブルにデータがコピーできていることを確認します。

select * from users;
実行結果
dev=# select * from users;
 userid | username |  firstname  |  lastname   |          city          | state |                             email                              |     phone      | likesports | liketheatre | likeconcerts | likejazz | likec
lassical | likeopera | likerock | likevegas | likebroadway | likemusicals
--------+----------+-------------+-------------+------------------------+-------+----------------------------------------------------------------+----------------+------------+-------------+--------------+----------+------
---------+-----------+----------+-----------+--------------+--------------
     65 | ZIX73XEV | Allegra     | Ford        | Durant                 | NE    | augue.scelerisque@pellentesque.edu                             | (512) 399-1427 | f          | f           |              | t        | f
         | f         | f        | f         | t            |
     90 | JFT95KJK | Yuli        | Gordon      | Lockport               | PE    | diam.eu@egestas.com                                            | (253) 868-2942 |            | f           | f            | f        | f
         |           | t        |           |              |
    153 | BLE66OZT | Lysandra    | Mays        | Bradford               | NJ    | lacinia.at@conguea.com                                         | (989) 933-0389 |            | t           |              |          | f
         |           |          | t         | f            |
    169 | AYQ83HGO | Deborah     | Watts       | Cranston               | NS    | non@quamdignissimpharetra.ca                                   | (400) 392-9599 | t          |             | t            |          |
         |           | t        | t         | t            | t
    178 | FSR62FMY | Cadman      | Contreras   | Connellsville          | NL    | libero.Proin.sed@erat.edu                                      | (592) 583-1313 | f          | t           | f            | t        | t
         |           |          | f         |              | f
    470 | OWW30MBN | Karen       | Downs       | El Segundo             | NB    | scelerisque.lorem@non.edu                                      | (153) 812-9933 |            | f           |              |          |
         |           |          | f         | f            | t
    495 | YFL41NTF | Piper       | Owens       | New Kensington         | MI    | orci.sem.eget@vulputatemauris.com                              | (792) 334-3612 | t          |             |              |          |
         | f         |          | t         | t            |
    580 | ICV16HHG | Macy        | Macdonald   | Nashua                 | SK    | purus.accumsan@aliquetsemut.org                                | (651) 298-5565 |            | t           |              |          |
         | t         | t        |           |              |
    630 | UGG16VZI | Dominic     | Lara        | Berkeley               | SK    | libero.dui@adipiscingelit.edu                                  | (859) 910-2655 |            |             | t            |          | f
         | f         | t        | t         |              |
    899 | MFA91UJF | Nolan       | Wagner      | Newport                | NB    | consequat@commodotincidunt.org                                 | (665) 873-9796 |            |             | f            | t        |
         |           | t        | f         | t            | f
    927 | LYW95MVY | Zenia       | Spears      | Laguna Woods           | NU    | tincidunt@a.edu                                                | (852) 513-2266 |            |             | t            | f        | t
         |           |          | f         | t            | f
   1107 | JSC53YST | Nehru       | Zimmerman   | St. George             | WA    | dictum@vestibulum.ca                                           | (241) 860-4761 |            | f           | f            |          | f
         |           | t        | t         | t            | f
   1200 | PAZ27CVG | Adrian      | Montgomery  | Gastonia               | MB    | Vestibulum@cursuspurusNullam.com                               | (482) 681-0574 |            |             |              | f        |
         |           |          | t         | t            | f
   1358 | IOC19RQZ | Adria       | Wilkerson   | Tamuning               | IA    | Nullam@Aliquamerat.org                                         | (387) 762-5293 | f          | f           | t            |          | f
         |           |          | t         | t            |
   1450 | EHJ00JCM | Halee       | Nichols     | Sierra Madre           | NV    | tempor@accumsan.com                                            | (504) 729-9651 |            |             | f            | t        |
         |           |          | t         |              | f
   1525 | PMH76YTD | Winter      | Carson      | Fall River             | SK    | eget.venenatis@Nullasemper.ca                                  | (414) 918-4478 |            |             | f            | t        | t
         | f         |          | f         |              | f
   1629 | WJL01VXJ | Emma        | Collins     | Haverhill              | QC    | venenatis@adipiscingelit.com                                   | (108) 763-6635 |            |             | f            | f        |
         | t         | f        | f         |              | f
   1740 | JPL20IUP | Mercedes    | Lawson      | Clovis                 | ON    | Vivamus.nibh@auctorvitae.edu                                   | (592) 279-2280 |            |             | t            | f        |
         | t         |          | t         |              |
   1834 | VRY12JJD | Simon       | Zimmerman   | El Paso                | AB    | luctus@ideratEtiam.com                                         | (378) 156-4059 | t          | f           | t            | t        |
         | f         |          | f         |              |
   1835 | TIV63IIU | Helen       | Sykes       | San Antonio            | MS    | Etiam.vestibulum@ligulaconsectetuer.ca                         | (817) 234-0455 |            |             |              |          |
         |           | f        | f         | t            | f
   2411 | LRW93URI | Hedley      | Bauer       | Uniontown              | WV    | Mauris.quis.turpis@eget.edu                                    | (918) 861-5492 | t          | f           | t            | f        |
         | f         |          |           |              |
   2568 | QUE33NIO | Griffin     | Lowe        | Buffalo                | OK    | leo.Cras.vehicula@felisNullatempor.com                         | (550) 140-4482 |            | t           | f            |          | t
         | f         | t        | t         | f            |
   2703 | PNQ73KQY | Martina     | Webb        | Phenix City            | CA    | congue@ac.ca                                                   | (734) 488-7841 | f          | f           |              | f        |
         |           |          |           | t            |
   2749 | YLO15ZCR | Simone      | Richmond    | Oneonta                | QC    | lorem@utnulla.com                                              | (657) 355-7479 |            | t           | t            |          |
         | t         | f        | f         | t            |
   2874 | XYF35GUM | Zeus        | Clarke      | Concord                | ID    | neque@Proin.ca                                                 | (798) 412-2960 | t          | t           |              |          |
         |           | f        |           | t            |
   3389 | LUG74HVE | Lillith     | Aguilar     | Springfield            | KY    | ac@metusVivamuseuismod.edu                                     | (742) 870-7192 |            | f           |              |          |
         | f         | t        | f         | f            | f
   3604 | YUC75ZEU | Zachery     | Molina      | Manhattan Beach        | NB    | fermentum@Nulla.com                                            | (419) 121-8814 | f          |             | f            |          |
         |           |          | t         | f            |
   3727 | RHK74MUQ | Bertha      | Preston     | Brownsville            | AZ    | ornare.lectus@nislsem.ca                                       | (434) 744-4958 |            | t           |              |          |
         | t         | f        | t         | t            |
   3757 | OXK64PWT | Phyllis     | Davidson    | Glens Falls            | TX    | quam.Pellentesque.habitant@adipiscingfringilla.edu             | (705) 682-4634 |            | f           |              | f        |
         | t         | f        | f         | f            | t
   3902 | ECM43HQF | Sandra      | Steele      | Hope                   | PE    | non.lobortis@nonfeugiatnec.com                                 | (809) 122-1188 | t          |             |              |          |
         |           | f        | t         | t            | t
   3964 | JKP05CQK | Zeus        | Rojas       | Fernley                | MA    | Mauris@massa.ca                                                | (480) 266-8551 | t          |             | t            | t        |
         | t         |          |           |              |
   4016 | UIL67ZYG | Jena        | Allen       | Concord                | VT    | ligula.consectetuer.rhoncus@duiSuspendisseac.com               | (179) 170-4780 |            | t           | t            |          | t
         |           | f        |           | t            |
   4039 | ESD59CSN | Holmes      | Thornton    | West Sacramento        | MO    | In.faucibus@purusmauris.org                                    | (352) 988-2558 |            | t           |              | f        |
         | f         |          | t         |              |
   4113 | OTI34SVV | Reed        | Battle      | North Tonawanda        | CO    | dui@Inlorem.ca                                                 | (428) 165-1087 |            |             |              |          |
         | f         | t        | t         | f            |
   4187 | PDM39FKI | Finn        | Payne       | Signal Hill            | ID    | Aliquam@mattisvelit.com                                        | (463) 262-7680 | t          | t           |              | t        |
         | f         | f        |           |              |
   4234 | EXE91OAM | Branden     | Townsend    | Newark                 | KS    | in@Etiamligulatortor.ca                                        | (842) 613-9904 |            | f           | t            |          | t
         |           |          | t         | t            | t
   4371 | YTW48TFS | Isaac       | Clements    | Geneva                 | NL    | vestibulum@maurisblanditmattis.ca                              | (142) 117-0753 | f          |             | t            |          |
         |           |          |           | t            | f
   4403 | PTG84CME | Ivor        | Sharpe      | Oneonta                | IA    | vulputate.nisi.sem@idmollisnec.org                             | (872) 572-1082 | f          | f           | t            | t        |
         |           | f        |           | f            |
   4445 | DPA31FQW | Lucian      | Velez       | Somerville             | NB    | Vivamus.nibh.dolor@dolorFuscemi.org                            | (816) 382-7036 | t          | f           | f            | f        | f
         | t         |          |           | f            |
   4495 | SPP96AAY | Patience    | Davenport   | Baltimore              | YT    | Duis.sit@vitaediam.edu                                         | (468) 330-1084 |            |             | f            |          |
         |           |          |           | t            |
   4638 | EMN73KBG | Uriel       | Schmidt     | Casper                 | BC    | sem@Morbi.ca                                                   | (748) 982-9228 |            | t           | t            |          | f
         |           |          | t         |              | f
   4737 | CJH81LQN | Phillip     | Kane        | Brea                   | CO    | a@pedeSuspendisse.org                                          | (166) 488-4774 |            | t           | f            | f        |
         |           |          | t         | f            |
   4777 | LGX94AAE | Emerson     | Merritt     | Williamsburg           | DE    | est@Nullasemper.org                                            | (905) 489-5417 | t          |             | f            | t        |
         |           | t        | t         |              |
   5392 | ABQ97SWG | Jerry       | Montgomery  | Grand Island           | MB    | Nam.ligula@Quisque.edu                                         | (816) 449-9956 | t          | f           | t            | f        | f
         |           |          | t         |              | t
   5590 | TOM62FZV | Slade       | Finley      | Sandy                  | NS    | sodales.purus@vitaedolorDonec.edu                              | (762) 181-5390 |            | t           |              | f        | t
         | t         |          |           |              | f
   5599 | WBU09ARH | McKenzie    | Mullen      | Hartford               | VA    | ante@aultricies.org                                            | (697) 479-7134 |            |             |              |          | t
         | f         |          | f         |              |
--More--Cancel request sent
   6098 | PPK51EWX | Kitra       | Livingston  | Fulton                 | ME    | ac@dignissim.ca                                                | (943) 490-6139 |            | t           |              | t        | f
dev=#

大量のユーザーデータがコピーできています。私は途中で「Ctr + C」を押下して出力を止めました。

これで、Redshift Serverless にデータコピーするところまで完了しました。

5. QuickSight の設定

QuickSight を設定していきます。
visualize-redshift-in-another-region-from-quicksight_1

5-1. QuickSight の VPC 設定に使う IAM ロールの作成

QuickSight が VPC 内のリソースにアクセスするためには VPC 内に QuickSight が QuickSight 用 ENI を作成する必要があります。

以下のポリシーを付与した「quicksight-vpc-role」を作成してください。

信頼ポリシー(信頼関係)
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "Service": "quicksight.amazonaws.com"
            },
            "Action": "sts:AssumeRole"
        }
    ]
}

許可

quicksight-vpc-policy
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "ec2:CreateNetworkInterface",
                "ec2:ModifyNetworkInterfaceAttribute",
                "ec2:DeleteNetworkInterface",
                "ec2:DescribeSubnets",
                "ec2:DescribeSecurityGroups"
            ],
            "Resource": "*"
        }
    ]
}

https://docs.aws.amazon.com/ja_jp/quicksight/latest/user/vpc-creating-a-connection-in-quicksight-console.html

補足:QuickSight のサービスリンクロール

QuickSight が自動で作成するサービスリンクロールには以下の二つが存在します。

  • aws-quicksight-service-role-v0
  • aws-quicksight-secretsmanager-role-v0

https://dev.classmethod.jp/articles/iam-how-to-identify-service-linked-roles/

これらのサービスリンクロールをユーザーが直接編集すると、QuickSight の管理画面で「QuickSight の AWS のサービスへのアクセス」を編集する際にエラーになってしまうことがあります。
そのため今回は QuickSight が VPC を操作するために別途「quicksight-vpc-role」を作成しました。

もしサービスリンクロールを直接手で編集してしまった場合は、以下 re:Post 記事を参考に、サービスリンクロールと、それに付与されている IAM ポリシーを一旦削除して、再度 QuickSight 側で自動作成されるのを待ちましょう。
https://repost.aws/ja/knowledge-center/quicksight-permission-errors

5-2. QuickSight の VPC 設定

QuickSight コンソールを開き、右上のユーザーアイコンから「QuickSight を管理」をクリックします。
visualize-redshift-in-another-region-from-quicksight_21

「VPC 接続の管理」をクリックし、
visualize-redshift-in-another-region-from-quicksight_22

「VPC 接続の追加」をクリックします。
visualize-redshift-in-another-region-from-quicksight_23

以下のように設定しました。

項目 設定値 備考
VPC 接続名 virginia-connection 任意の名前
VPC ID vpc-xxxxxxxxxxxxxxxxx Tokyo-vpc の VPC ID
実行ロール quicksight-vpc-role 5-1. で作成した IAM ロール
サブネット (少なくとも 2 つを選択) 東京リージョンに作成した VPC 内のプライベートサブネット 2 つを選択
セキュリティグループ ID sg-xxxxxxxxxxxxxxxxx quicksight-sg

visualize-redshift-in-another-region-from-quicksight_24

以下のように VPC 接続が作成されました。ステータスは最初「UNAVAILABLE」になっていますが、少し待つと「ABAILAVLE」になります。
visualize-redshift-in-another-region-from-quicksight_25

5-3. データセット、分析、ダッシュボードの作成

データセット画面で「新しいデータセット」をクリックします。
visualize-redshift-in-another-region-from-quicksight_26

Redshift 手動検出 を選択します。自動検出ではありません。
visualize-redshift-in-another-region-from-quicksight_27

項目 設定値 備考
データソース名 Virginia-Redshift 任意の名前
接続タイプ virginia-connection 5-2. で作成済みの VPC 接続を選択
データベースサーバー 20241106-workgroup.123456789012.us-east-1.redshift-serverless.amazonaws.com Redshift Serverless のワークグループホスト名
ポート 5439
データベース名 dev
ユーザー名 admin
パスワード Secrets Manager からコピー 本記事では直接パスワードを入力しますが、後ほど Secrets Manager を利用する方法を紹介する記事を作成します。追記:執筆しました。QuickSight とは別リージョンの Redshift や RDS のデータを可視化する際、認証情報として Secrets Manager を使う方法 | DevelopersIO

SSL 接続の検証も OK のはずです。「データソースを作成」をクリックします。

visualize-redshift-in-another-region-from-quicksight_28

public スキーマを選択し、users テーブルにチェックを入れ「データの編集/プレビュー」をクリックします。
visualize-redshift-in-another-region-from-quicksight_29

Redshift Serverless 内の users テーブルが画面下部に表示されるのが確認できます。
今回クエリモードは「直接クエリ(ダイレクトクエリ)」のままにしておきます。「保存して視覚化」をクリックし、分析を作成します。
visualize-redshift-in-another-region-from-quicksight_30

今回のビジュアルは垂直積み上げ棒グラフで、state(州)ごとの likebroadway(ブロードウェイ好きの人)を視覚化してみました。「公開」をクリックし、ダッシュボードを作成します。
visualize-redshift-in-another-region-from-quicksight_31

新しいダッシュボードとして名前を付け、「ダッシュボードの公開」をクリックします。
visualize-redshift-in-another-region-from-quicksight_32

バージニア北部リージョンの Redshift Serverless のデータを、東京リージョンの QuickSight で可視化しダッシュボード作成できました。
visualize-redshift-in-another-region-from-quicksight_33

おわりに

QuickSight で別リージョンの Redshift のデータを可視化してみました。
背景としては、Q in QuickSight がまだ東京リージョンでサポートされておらず、バージニア北部リージョンで Q in QuickSight を有効化しつつ東京リージョンのリソースを可視化したいなと思ったことがきっかけです。
今回は東京リージョンの QuickSight でバージニア北部リージョンのデータを可視化しましたが、設定値を変えればリージョンが逆でもできそうなことが分かったので良かったです。

参考

https://repost.aws/ja/knowledge-center/quicksight-redshift-private-connection

https://aws.amazon.com/jp/blogs/big-data/amazon-quicksight-deployment-models-for-cross-account-and-cross-region-access-to-amazon-redshift-and-amazon-rds/

脚注
  1. 現在は Amazon S3 からデータをロードする に記載されているように、AWS が管理している S3 バケットからクエリエディタ v2 でサンプルデータを使う手順となっているようです。 ↩︎

Share this article

facebook logohatena logotwitter logo

© Classmethod, Inc. All rights reserved.